Step 1: Prepare the Data for PivotTable Report

You're probably anxious to get started on your first PivotTable report. But before you begin, it's a good idea to make sure your data is well organized and ready to go.

In this article, you'll learn what kinds of data you can use to create a PivotTable report, and what you need to do to prepare it. Once you've prepared the data, you can create the PivotTable report by using the PivotTable and PivotChart Wizard. In the wizard, you'll actually choose the data source. (You can choose data already entered in Excel, or data from a database, called an "external data source.") If you've chosen an external data source, the wizard lets you use a separate program called Microsoft Query to connect to the data source and select just the data you want.

What Types of Data You Can Use

·         Excel list or database

·         External data source   For example, you can use a database file, text file, or a source on the Internet. For more information about data sources, type types of databases in the Office Assistant or on the Answer Wizard tab in the Excel Help window, and then click Search.

·         Multiple consolidation ranges   You can combine and summarize data from several different Excel lists.

·         Another PivotTable report   Do you plan to use the same data to create multiple PivotTable reports? If so, you can save memory and disk space by reusing a copy of the data from an existing PivotTable report to create a new PivotTable report. This action permanently links both the original and new PivotTable reports. (For example, if you refresh the data in the original PivotTable report, the data in the new report is also refreshed, and vice versa.)

What You Need to Do to Prepare the Data

To prepare the data for a PivotTable report, just read that section below that corresponds to the type of data you're using.

Prepare Data from an Excel List or Database

There are a few things to consider in preparing your Excel data:

·         Make sure the list is well organized   Neatness counts! For example, make sure the first row of the list contains column labels, since Excel will use this data for the field names in the report. Also, make sure each column contains similar items - for example, include text in one column and numeric values in a separate column.

·         Remove all automatic totals   Don't worry - the PivotTable report will calculate the subtotals and grand totals for you.

·         If you plan to add more data later, create a named range   Then, when you create the PivotTable report, make sure to specify the named range. That way, whenever you add more source data, you can update the PivotTable report to include the new data.

·         If you want to include filtered data, use the Advanced Filter command   On the Data menu, point to Filter, click Advanced Filter, and then click Copy to another location. This extracts the filtered data to another worksheet location, so you can use it in your PivotTable report. Don't filter the data in place by using the AutoFilter command, or by using the Filter the list, in-place option of the Advanced Filter command. This merely changes your view of the data (not the data itself), and the PivotTable report will include all the data in the list.

Prepare Data from an External Data Source

There are a few things to consider in preparing your external data:

·         Install the necessary tools and drivers   You're probably all ready to go - just make sure you've installed Microsoft Query and the appropriate open database connectivity (ODBC) drivers or data source drivers you need. (If you're not sure how to do this, type install Microsoft Query in the Office Assistant or on the Answer Wizard tab in the Excel Help window, click Search and then click "What you need to retrieve external data"). Then, when you create a PivotTable report by using the PivotTable and PivotChart Wizard, you'll use Microsoft Query to actually retrieve the external data.

·         If applicable, get more details on OLAP source data   If you're retrieving source data from an On-Line Analytical Processing (OLAP) database or cube file, you can find out more about the OLAP features of Excel by typing create a PivotTable report from OLAP source data in the Office Assistant or on the Answer Wizard tab in the Excel Help window, and then clicking Search.

·         In the following cases, retrieve the data before starting the wizard   Check the following list - if it contains your data retrieval method, you won't be able to use Query from within the PivotTable and PivotChart Wizard to retrieve the data. Instead, use the following instructions to insert the data in an Excel workbook. You can then use the wizard to select the worksheet range that contains the external data, just as you would any other Excel list.

·         Query files and report templates   To use a query (.dqy) file to retrieve the data, open the query file (for more information, type create .dqy files in the Office Assistant or on the Answer Wizard tab in the Excel Help window, and then click Search). To use a report template (.xlt) that doesn't already include a PivotTable report, open the template (for more information, type ways to share external data in the Office Assistant or on the Answer Wizard tab in the Excel Help window, and then click Search).

·         Parameter queries   To use a parameter query to retrieve the data, you must first create the parameter query (for more information, type create a query that prompts for criteria in the Office Assistant or on the Answer Wizard tab in the Excel Help window, and then click Search). Note that you can't create a parameter query to retrieve source data from OLAP databases.

·         Web queries   To use a Web query to retrieve data over the  Internet, you must first create a Web query (for more information, type create a Web query in the Office Assistant or on the Answer Wizard tab in the Excel Help window, and then click Search).

Prepare Data from Multiple Consolidation Ranges

There are a few things to consider in preparing data from multiple Excel lists:

·         Make sure the lists are well organized   Neatness counts! For example, make sure the lists have matching row and column names for items you want to summarize together.

·         Remove all automatic totals   Don't worry - the PivotTable report will calculate the subtotals and grand totals for you.

·         If you plan to add more data later, create named ranges   Then, when you create the PivotTable report, make sure to specify the named ranges. That way, whenever you add more source data, you can update the PivotTable report to include the new data.

Prepare Data from Another PivotTable Report

There are a few things to consider in preparing data from another PivotTable report:

·         Make sure both PivotTable reports are in the same workbook   If the original PivotTable report is in a different workbook, copy the original report to the workbook where you want the new report to appear.

·         Check the page field settings   In the original PivotTable report, you may have changed the page field settings so they retrieve external data for each page field item individually. In this case, you need to reset the page fields so they retrieve external data for all items at once. (For more information, type retrieve PivotTable page field data in the Office Assistant or on the Answer Wizard tab in the Excel Help window, and then click Search.)